
package UTGBusiness;

import XMLForms.TCM;
import XMLForms.TTRM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author yuxia
 */
public class TDManager {

    private Connection conn;

    public TDManager(){

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/"
                    + "mysql", "root", "12345");
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public boolean hasEntry(String seq) throws SQLException {
        String sql = "SELECT * FROM transaction WHERE SequenceNumber=" + "'" + seq + "'";
        Statement stmt = null;
        ResultSet rs = null;
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        return rs.next();
    }

    public boolean hasTimeExceeded(String seq){
        try {
            String sql = "SELECT * FROM transaction WHERE SequenceNumber=" + "'" + seq + "'";
            Statement stmt = null;
            ResultSet rs = null;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (!rs.next()) return false;
            if (rs.getString("tradeState").equals("Exceeded")){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(TDManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public void updateDBTR(String seq, String status){
        try {
            String sql = "UPDATE transaction SET tradestate=" + "'" + status + "'" + 
                    " WHERE SequenceNumber=" + "'" + seq + "'";
            Statement stmt = null;
            int rs;
            stmt = conn.createStatement();
            rs = stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(TDManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void addDBTR(TTRM ttrm, String firmID, String firmName){
        try {
            String sql = "INSERT INTO transaction (traderID, PerSharePrice , "
                    + "StockID, OffererID , "
                    + "OfferTime , BuySell, RequestTime, tradeState, "
                    + "ModificationTime, SequenceNumber, ShareCount, FirmID) "
                    + "VALUES (" + "'" + ttrm.getTraderID() + "'" + "," + 
                    String.valueOf(ttrm.getSharePrice())
                    + "," + "'" + ttrm.getStockID() + "'" + "," + "'" + ttrm.getOffererID() + "'" + "," + 
                    ttrm.getOfferTime() + "," + "'" + ttrm.getBuySell() + "'" + "," 
                    + ttrm.getTraderTimeSentStamp()  + "," + "'" + "Request Made" + "'"  + "," 
                    + new Date().getTime() + "," + "'" + firmID + "-" + firmName 
                    + "-" + ttrm.getTraderTimeSentStamp() + "'" + "," 
                    + ttrm.getShareCount() + "," + "'" + firmID + "'" +")"; 
            Statement stmt = null;
            int rs;
            stmt = conn.createStatement();
            rs = stmt.executeUpdate(sql);
            //rs = stmt.executeUpdate("INSERT INTO transaction (sequenceNumber) VALUES (" + "'" + firmID + "-" + firmName 
            //        + "-" + ttrm.getTraderTimeSentStamp() + "'" + ")");
            System.out.println("Data Add Successful");
        } catch (SQLException ex) {
            System.out.println(ex);
            Logger.getLogger(TDManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void addDBTR(TCM tcm, String firmID, String firmName){
        try {
            String sql = "INSERT INTO transaction (traderID, PerSharePrice , "
                    + "StockID, OffererID , "
                    + "OfferTime , BuySell, RequestTime, tradeState, "
                    + "ModificationTime, SequenceNumber, ShareCount, FirmID) "
                    + "VALUES (" + "'" + tcm.getTtrm().getTraderID() + "'" + "," + 
                    String.valueOf(tcm.getTtrm().getSharePrice())
                    + "," + "'" + tcm.getTtrm().getStockID() + "'" + "," + "'" + tcm.getTtrm().getOffererID() + "'" + "," + 
                    tcm.getTtrm().getOfferTime() + "," + "'" + tcm.getTtrm().getBuySell() + "'" + "," 
                    + tcm.getTtrm().getTraderTimeSentStamp()  + "," + "'" + "Request Made" + "'"  + "," 
                    + new Date().getTime() + "," + "'" + tcm.getFirmSequencNumber() + "'" + "," 
                    + tcm.getTtrm().getShareCount() + "," + "'" + firmID + "'" +")"; 
            Statement stmt = null;
            int rs;
            System.out.println(sql);
            stmt = conn.createStatement();
            rs = stmt.executeUpdate(sql);
            //rs = stmt.executeUpdate("INSERT INTO transaction (sequenceNumber) VALUES (" + "'" + firmID + "-" + firmName 
            //        + "-" + ttrm.getTraderTimeSentStamp() + "'" + ")");
            System.out.println("Data Add Successful");
        } catch (SQLException ex) {
            System.out.println(ex);
            Logger.getLogger(TDManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void updateModificationTime(String seq){
        try {
            String sql = "UPDATE transaction SET modificationTime=" + new Date().getTime() + 
                    " WHERE SequenceNumber=" + "'" + seq + "'";
            Statement stmt = null;
            int rs;
            stmt = conn.createStatement();
            rs = stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(TDManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void updateAcceptorID(String seq, String AID) {
        try {
            String sql = "UPDATE transaction SET acceptorID=" + "'" + AID + "'"
                    + " WHERE SequenceNumber=" + "'" + seq + "'";
            Statement stmt = null;
            int rs;
            System.out.println(sql);
            stmt = conn.createStatement();
            rs = stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(TDManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
